﻿IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.RemindersQueue') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.RemindersQueue
		(
		RemindersQueueID int IDENTITY(1,1) NOT NULL,
		RemindersQueueName nvarchar(200) NULL,
		SiteCollectionID uniqueidentifier NOT NULL,
		SiteCollectionJobIdentifier uniqueidentifier NOT NULL,
		ApprovalRequestAttempts int DEFAULT(0) NOT NULL,
		Approved bit DEFAULT(0) NOT NULL,
		IsBeingProcessed bit DEFAULT(0) NOT NULL,
		Completed bit DEFAULT(0) NOT NULL,
		HasErrors bit DEFAULT(0) NOT NULL,
		Created datetime DEFAULT(GETDATE()) NOT NULL,
		Modified datetime DEFAULT(GETDATE()) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.RemindersQueue WITH NOCHECK ADD 
		CONSTRAINT [PK_RemindersQueue] PRIMARY KEY NONCLUSTERED 
			(
				RemindersQueueID
			)
	ON [PRIMARY]
END
IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.RemindersQueueItem') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.RemindersQueueItem
		(
		RemindersQueueItemID int IDENTITY(1,1) NOT NULL,
		RemindersQueueID int NOT NULL,
		SiteID uniqueidentifier NOT NULL,
		SiteTitle nvarchar(200) NOT NULL,
		SiteUrl nvarchar(1000) NOT NULL,
		FromAddress nvarchar(500) NOT NULL,
		PreservationNoticeTemplateID int NOT NULL,
		OriginalPreservationNoticeHistoryID int NOT NULL,	
		MessageSubject nvarchar(500) NOT NULL,
		ReminderType nvarchar(50) NOT NULL,
		CustodianID int NOT NULL,
		CustodianEmail nvarchar(500) NOT NULL,
		CustodianFirstName nvarchar(200) NOT NULL,
		CustodianLastName nvarchar(200) NOT NULL,
		ProcessingAttempts int DEFAULT(0) NOT NULL,
		Completed bit DEFAULT(0) NOT NULL,
		HasErrors bit DEFAULT(0) NOT NULL,
		Errors nvarchar(3000) NULL,
		Created datetime DEFAULT(GETDATE()) NOT NULL,
		Modified datetime DEFAULT(GETDATE()) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.RemindersQueueItem WITH NOCHECK ADD 
		CONSTRAINT [PK_RemindersQueueItem] PRIMARY KEY CLUSTERED 
		(
			RemindersQueueItemID
		)
	ON [PRIMARY] 
	
	ALTER TABLE dbo.RemindersQueueItem ADD 
		CONSTRAINT [FK_RemindersQueueItem_RemindersQueue] FOREIGN KEY 
		(
			RemindersQueueID
		) 
		REFERENCES dbo.RemindersQueue
		(
			RemindersQueueID
		)
		
	ALTER TABLE dbo.RemindersQueueItem ADD 
		CONSTRAINT [FK_RemindersQueueItem_PreservationNoticeHistoryID] FOREIGN KEY 
		(
			OriginalPreservationNoticeHistoryID
		) 
		REFERENCES dbo.PreservationNoticeHistory
		(
			PreservationNoticeHistoryID
		)
END

IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.RejectedRemindersQueue') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.RejectedRemindersQueue
		(
		RemindersQueueID int NOT NULL,
		RemindersQueueName nvarchar(200) NULL,
		SiteCollectionID uniqueidentifier NOT NULL,
		SiteCollectionJobIdentifier uniqueidentifier NOT NULL,
		ApprovalRequestAttempts int DEFAULT(0) NOT NULL,
		Approved bit DEFAULT(0) NOT NULL,
		IsBeingProcessed bit DEFAULT(0) NOT NULL,
		Completed bit DEFAULT(0) NOT NULL,
		HasErrors bit DEFAULT(0) NOT NULL,
		Created datetime DEFAULT(GETDATE()) NOT NULL,
		Modified datetime DEFAULT(GETDATE()) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.RejectedRemindersQueue WITH NOCHECK ADD 
		CONSTRAINT [PK_RejectedRemindersQueue] PRIMARY KEY NONCLUSTERED 
			(
				RemindersQueueID
			)
	ON [PRIMARY]
END
IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.RejectedRemindersQueueItem') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.RejectedRemindersQueueItem
		(
		RemindersQueueItemID int NOT NULL,
		RemindersQueueID int NOT NULL,
		SiteID uniqueidentifier NOT NULL,
		SiteTitle nvarchar(200) NOT NULL,
		SiteUrl nvarchar(1000) NOT NULL,
		FromAddress nvarchar(500) NOT NULL,
		PreservationNoticeTemplateID int NOT NULL,
		OriginalPreservationNoticeHistoryID int NOT NULL,	
		MessageSubject nvarchar(500) NOT NULL,
		ReminderType nvarchar(50) NOT NULL,
		CustodianID int NOT NULL,
		CustodianEmail nvarchar(500) NOT NULL,
		CustodianFirstName nvarchar(200) NOT NULL,
		CustodianLastName nvarchar(200) NOT NULL,
		ProcessingAttempts int DEFAULT(0) NOT NULL,
		Completed bit DEFAULT(0) NOT NULL,
		HasErrors bit DEFAULT(0) NOT NULL,
		Errors nvarchar(3000) NULL,
		Created datetime DEFAULT(GETDATE()) NOT NULL,
		Modified datetime DEFAULT(GETDATE()) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.RejectedRemindersQueueItem WITH NOCHECK ADD 
		CONSTRAINT [PK_RejectedRemindersQueueItem] PRIMARY KEY CLUSTERED 
		(
			RemindersQueueItemID
		)
	ON [PRIMARY] 
END
GO 